import copy
import json
import uuid
import random
import xlsxwriter
from io import BytesIO
from apis import api_routes
from flask import g, request, jsonify, abort, Response
from security.decoration import certification
from public.local import local_time
from datetime import datetime, timedelta
from sqlalchemy import and_
from typing import List
from typing import Union
from sqlalchemy import text
from urllib.parse import unquote, quote
from models.order.tasks import Tasks
from models.product.routes import Routes
from models.course.detailed import Detailed
from models.facility.facility import Facility
from public.statistics import calculate_cpk
from models.product.aircraft import Aircraft


@api_routes.route("/query/mainsn", methods=["GET"])
def func_query_mainsn_get_api():
    """
    按照产品SN码查询过站所有数据, (工单、机种、产品SN、日期可以自由组合查询) 完成API开发
    这个表数据量最多，可能上亿数据， 如果不指定日期，查询相对比较慢，前端最好做个默认日期（前面60天到今天）
    http://127.0.0.1:8080/api/query/mainsn
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int      ****   设备ID，没有此参数则是全部查询      /api/query/facility  GET
    "task_id"       int             工单名称ID，没有此参数则是全部查询   /api/query/task/kind GET
    "aircraft_id"   int             机种名称ID，没有此参数则是全部查询   /api/query/aircraft/kind GET
    "sequence"      string          产品SN，不支持模糊查询
    "start"         string          开始时间, 默认前面30天  格式 %Y-%m-%d
    "finish"        string          结束时间，默认今天时间  格式 %Y-%m-%d
    "index"         int      ****   第几页，默认为1
    "size"          int      ****   每页数据多少, 默认每页10条数据
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品SN查询所有数据
    """
    size: int = request.args.get("size", default=10, type=int)
    index: int = request.args.get("index", default=1, type=int)
    facility_id: int = request.args.get("facility_id", default=0, type=int)
    task_id: Union[int, None] = request.args.get("task_id", default=0, type=int)
    arg_aircraft_id: Union[int, None] = request.args.get("aircraft_id", default=0, type=int)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)

    if sequence is not None and len(sequence) == 0:
        sequence = None

    if start is not None and len(start) != 0 and finish is not None and len(finish) != 0:
        arg0 = datetime.strptime(start, "%Y-%m-%d")
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
        finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")
    else:
        start = None
        finish = None

    sql_count: Union[str, None] = """
        SELECT
            COUNT(courses.id)
        FROM courses WHERE courses.id > 0 {} {} {} {}
    """

    sql_context: Union[str, None] = """
        SELECT
            tb_first.id, tasks.orders, tasks.name, aircraft.aircraft, tb_first.sequence,
        CASE
            WHEN tb_first.mark = 0 THEN '未加工完'
            WHEN tb_first.mark = 1 THEN '全部合格'
            WHEN tb_first.mark = 2 THEN '不合格'
            WHEN tb_first.mark = 3 THEN '报废产品'
            ELSE '未知测试结果'
        END AS sn_status
        FROM
        (
        SELECT
            courses.*
        FROM courses WHERE courses.id > 0 {} {} {} {} ORDER BY courses.id LIMIT :arg2 OFFSET :arg3
        )AS tb_first
        INNER JOIN tasks ON tb_first.tasks_id = tasks.id
        INNER JOIN aircraft ON tb_first.aircraft_id = aircraft.id 
        ORDER BY tb_first.id
    """
    parm2, parm3, parm4, parm5 = "", "", "", ""
    if task_id != 0:
        parm2 = "AND courses.tasks_id = :tk_id"
    if arg_aircraft_id != 0:
        parm3 = "AND courses.aircraft_id = :at_id"
    if sequence is not None:
        parm4 = "AND courses.sequence = :seq"
    if start is not None and finish is not None:
        parm5 = "AND courses.start >= :arg0 AND courses.start <= :arg1"

    sql_count = sql_count.format(parm2, parm3, parm4, parm5)
    sql_context = sql_context.format(parm2, parm3, parm4, parm5)

    parm: dict = {
        "ft_id": facility_id, "at_id": arg_aircraft_id,
        "tk_id": task_id, "arg0": start, "arg1": finish,
        "seq": sequence, "arg2": size, "arg3": ((index - 1) * size)
    }

    rsp: list = []
    count: int = g.session.execute(text(sql_count), parm).fetchone()[0]
    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)
    db_data = g.session.execute(text(sql_context), parm).fetchall()

    sql_detailed: str = """
        SELECT
            facility.name, facility.serials, 
        CASE
            WHEN tb_first.status = TRUE THEN '合格'
            WHEN tb_first.status = FALSE THEN '不合格'
            ELSE '未知测试结果'
        END AS sn_status, coalesce(to_char(tb_first.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
            tb_first.numbers, tb_first.message
        FROM
        (
        SELECT
            detailed.*
        FROM detailed WHERE detailed.course_id = :cour_id {}
        )AS tb_first
        INNER JOIN facility ON tb_first.facility_id = facility.id ORDER BY tb_first.id
    """

    for cour_id, f_orders, f_name, f_aircraft, f_sequence, f_status in db_data:

        init_parm = ""
        if facility_id > 0:
            init_parm = " AND detailed.facility_id = :ft_id"
        new_sql_detailed = sql_detailed.format(init_parm)

        f_station: list = []
        db_detailed = g.session.execute(text(new_sql_detailed), {"cour_id": cour_id, "ft_id": facility_id}).fetchall()
        for ft_name, ft_serial, fdt_status, fdt_time, fdt_num, fdt_message in db_detailed:
            if fdt_message is None:
                f_station.append({
                    "ft_name": ft_name,
                    "ft_serial": ft_serial,
                    "fdt_status": fdt_status,
                    "fdt_time": fdt_time,
                    "fdt_num": fdt_num,
                    "key": None,
                    "floor": None,
                    "value": None,
                    "upper": None
                })
            else:
                db_messages: dict = json.loads(fdt_message)
                db_messages: list = db_messages.get("data")
                for itr in db_messages:
                    f_station.append({
                        "ft_name": ft_name,
                        "ft_serial": ft_serial,
                        "fdt_status": fdt_status,
                        "fdt_time": fdt_time,
                        "fdt_num": fdt_num,
                        "key": itr.get("key"),
                        "floor": itr.get("floor"),
                        "value": itr.get("value"),
                        "upper": itr.get("upper")
                    })
        rsp.append({
            "aircraft": f_aircraft,
            "f_orders": f_orders,
            "f_name": f_name,
            "sequence": f_sequence,
            "f_status": f_status,
            "f_station": copy.deepcopy(f_station)
        })

    doc: dict = {
        "aircraft": "产品名称",
        "f_orders": "工单编号",
        "f_name": "工单任务名称",
        "sequence": "产品编码",
        "f_status": "产品状态",
        "f_station": {
            "ft_name": "设备名称",
            "ft_serial": "设备编号",
            "fdt_status": "产品状态",
            "fdt_time": "加工完成时间",
            "fdt_num": "第几次加工",
            "key": "测试项名称",
            "floor": "下限",
            "value": "实际值",
            "upper": "上限"
        }
    }
    return jsonify({'status': 200, 'data': rsp, "index": index, "size": size, "total": count, "page": page, "doc": doc})
